--region [dbo].[AddReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddReply]
GO

CREATE PROCEDURE [dbo].[AddReply]
	@TopicId int,
	@Body nvarchar(max),
	@FloorNo int,
	@ReplyFloorNo int,
	@PostUserid int,
	@PostUserName nvarchar(50),
	@Ip nvarchar(50),
	@PostTime datetime,
	@UpdateTime datetime,
    @GroupId int,
    @IsDel int,
	@ReplyFile nvarchar(256),
	@ReplyFileName nvarchar(256),
	@id int OUTPUT
AS

INSERT INTO [Reply] (
	[TopicId],
	[Body],
	[FloorNo],
	[ReplyFloorNo],
	[PostUserid],
	[PostUserName],
	[Ip],
	[PostTime],
	[UpdateTime],
    [GroupId],
    [IsDel],
	[ReplyFile],
	[ReplyFileName]
) VALUES (
	@TopicId,
	@Body,
	@FloorNo,
	@ReplyFloorNo,
	@PostUserid,
	@PostUserName,
	@Ip,
	@PostTime,
	@UpdateTime,
    @GroupId,
    @IsDel,
	@ReplyFile,
	@ReplyFileName
)

SET @id = SCOPE_IDENTITY()

GO
--endregion


--region [dbo].[UpdateReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateReply]
GO

CREATE PROCEDURE [dbo].[UpdateReply]
	@Id int,
	@TopicId int,
	@PostUserid int,
	@Body nvarchar(max),
	@Ip nvarchar(50),
	@UpdateTime datetime
AS

UPDATE [Reply] SET
	[Body] = @Body,
	[Ip] = @Ip,
	[UpdateTime] = @UpdateTime
WHERE
	[Id] = @Id AND 	[TopicId] = @TopicId AND [PostUserid] = @PostUserid

GO
--endregion


--region [dbo].[DelReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelReply]
GO

CREATE PROCEDURE [dbo].[DelReply]
	@ReplyId int
AS

UPDATE [Reply] SET
	[IsDel] = 1
WHERE
	[Id] = @ReplyId

GO
--endregion

--region [dbo].[DelReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelAllReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelAllReply]
GO

CREATE PROCEDURE [dbo].[DelAllReply]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[Reply]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetReply]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetReply]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetReply]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetReply]
GO

CREATE PROCEDURE [dbo].[GetReply]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	[Id],
	[TopicId],
	[Body],
	[FloorNo],
	[ReplyFloorNo],
	[PostUserid],
	[PostUserName],
	[Ip],
	[PostTime],
	[UpdateTime],
	[IsDel],
	[ReplyFile],
	[ReplyFileName]
FROM
	[Reply]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetReplyList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetReplyList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetReplyList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetReplyList]
GO

CREATE PROCEDURE [dbo].[GetReplyList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [Reply]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[Reply].[Id],
	[TopicId],
	[Body],
	[FloorNo],
	[ReplyFloorNo],
	[PostUserid],
	[PostUserName],
	''ReplyGender'' = [Gender],
	''ReplyAvatar'' = [Avatar],
	''ReplyAge'' = DATEDIFF(year, [UserInfo].[Birthday], getdate()),
	[Ip],
	[PostTime],
	[Reply].[UpdateTime],
	[Reply].[IsDel],
	[Reply].[ReplyFile],
	[Reply].[ReplyFileName]
FROM
	[Reply], [userinfo]
WHERE
	[Reply].[PostUserId] = [userinfo].[Id] 
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [Reply].[Id] > 
	( 
	SELECT MAX([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [Reply] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


